<?php

namespace App\Http\Controllers;

use App\Libs\CommonUtils;
use App\Models\Area;
use App\Models\Project;
use App\Models\ProjectHire;
use App\Models\Team;
use App\Models\UserProject;
use App\Models\UserTeam;
use App\Models\Volunteer;
use App\Models\WechatTempMsg;
use App\User;
use Carbon\Carbon;
use function GuzzleHttp\Psr7\str;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Mockery\Exception;

class StatisticsController extends Controller
{

    public function currentVolunteer(Request $request) {
        $user = $request->user();
        if($user->type == 2 )
        {
            $teamUserIds = $this->_getTeamUserId($user->team_id);
        }
        switch($user->type)
        {
            case 2:

                $data['volunteer_count'] = User::where('type', 1)->whereRaw("id in ({$teamUserIds})")->count();
                $data['volunteer_yesterday_count'] = User::where('type', 1)->whereRaw("id in ({$teamUserIds})")->where('created_at', 'like', date('Y-m-d', time()-86400))->count();


                break;

            case 3:
                $data['project_count'] = Project::where('org_id', $user->org_id)->count();
                $data['project_yesterday_count'] = Project::where('org_id', $user->org_id)->where('created_at', 'like', date('Y-m-d', time()-86400))->count();
                $data['volunteer_count'] = User::where('type', 1)->where('org_id', $user->org_id)->count();
                $data['volunteer_yesterday_count'] = User::where('type', 1)->where('org_id', $user->org_id)->where('created_at', 'like', date('Y-m-d', time()-86400))->count();
                $data['team_count'] = Team::where('org_id', $user->org_id)->count();
                $data['team_yesterday_count'] = Team::where('org_id', $user->org_id)->where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $data['reward_count'] = UserProject::with('project')->whereHas('project', function($query) use ($user){
                    $query->where('org_id', $user->org_id);
                })->sum('reward_time');
                $data['reward_yesterday_count'] = UserProject::with('project')->whereHas('project', function($query) use ($user){
                    $query->where('org_id', $user->org_id);
                })->where('created_at', 'like', date('Y-m-d', time()-86400))->sum('reward_time');

                break;

            case 4:
                $data['project_count'] = Project::query()->count();
                $data['project_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $data['volunteer_count'] = User::where('type', 1)->count();
                $data['volunteer_yesterday_count'] = User::where('type', 1)->where('created_at', 'like', date('Y-m-d', time()-86400))->count();
                $data['team_count'] = Team::query()->count();
                $data['team_yesterday_count'] = Team::where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $data['reward_count'] = UserProject::query()->sum('reward_time');
                $data['reward_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', time()-86400))->sum('reward_time');

                break;
            case 5:
                $data['project_count'] = Project::query()->count();
                $data['project_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $data['volunteer_count'] = User::where('type', 1)->count();
                $data['volunteer_yesterday_count'] = User::where('type', 1)->where('created_at', 'like', date('Y-m-d', time()-86400))->count();
                $data['team_count'] = Team::query()->count();
                $data['team_yesterday_count'] = Team::where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $data['reward_count'] = UserProject::query()->sum('reward_time');
                $data['reward_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', time()-86400))->sum('reward_time');

                break;
        }
        return $this->returnJSON($data, 0, '');
    }
    //志愿者 用户增长 统计分析
    public function increaseVolunteer(Request $request)
    {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if ($request->has('date_range')){
            $dateRange = array_map(function($item){
                return intval($item);
            }, explode(',', $request->get('date_range')));
            $begin = $dateRange[0];
            $end = $dateRange[1];
        } else {
            $begin = strtotime('-60 day');
            $end = time();
        }

        $beginDay = date('Y-m-d', $begin);
        $endDay = date('Y-m-d', $end);

        $list = [];
        $totalQuery = DB::table('user')->selectRaw('count(*) as ct')->where('type', '=', 1)->where('created_at', '<' , $beginDay);
        switch($user->type)
        {
            case 2:
                $teamUserIds = $this->_getTeamUserId($user->team_id);
                $list = DB::table(DB::raw('user as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct')->whereraw("type=1 and id in ({$teamUserIds}) and  DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                $totalQuery = $totalQuery->whereRaw("id in (".$teamUserIds.")")->get();
                break;

            case 3:
                $list = DB::table(DB::raw('user as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct')->whereraw("type=1 and org_id={$user->org_id} and  DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                $totalQuery = $totalQuery->where('org_id', '=', $user->org_id)->get();

                break;

            case 4:
                $list = DB::table(DB::raw('user as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct')->whereraw("type=1 and  DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'".($org_id ? "and org_id ={$org_id} " : ''))->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                if ($org_id) {
                    $totalQuery = $totalQuery->where('org_id', '=', $org_id);
                }
                $totalQuery = $totalQuery->get();

                break;
            case 5:
                $list = DB::table(DB::raw('user as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user as t2 where type=1 and DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')'.($org_id ? "and org_id ={$org_id} " : '').') as total ')->whereraw("type=1 and  created_at >= '{$beginDay}' and created_at <= '{$endDay}'".($org_id ? "and org_id ={$org_id} " : ''))->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        // 计算total
        foreach ($list as $item) {
            $totalQuery[0]->ct += $item->ct;
            $item->total = $totalQuery[0]->ct;
        }

        $dayCount = $this->_dayCount($list, $beginDay, $endDay);

        $addData = [];
        $totalData = [];
        $dateRange = [];

        foreach($dayCount as $item) {
            array_push($addData, $item['ct']);
            array_push($totalData, $item['total']);
            array_push($dateRange, $item['day']);
        }
//        dd($totalData);
        $chart = new CommonUtils();
        $data = $chart->userIncreaseChartConfig($dateRange, $addData, $totalData);
        return $this->returnJSON($data, 0, '');
    }

    // 志愿者 性别  统计分析
    public  function genderVolunteer(Request $request) {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);

        switch($user->type)
        {
            case 2 :
                $teamUserIds = $this->_getTeamUserId($user->team_id);
                $list = Volunteer::selectRaw('SUBSTR(id_number,17,1) %2 as sex, count(*)  as ct')->whereRaw("user_id in ({$teamUserIds})")->where('id_number', '!=', '')->groupBy(DB::raw('SUBSTR(id_number,17,1) %2'))->get();
                break;

            case 3:
                $list = Volunteer::selectRaw('SUBSTR(id_number,17,1) %2 as sex, count(*)  as ct')->where('org_id', $user->org_id)->where('id_number', '!=', '')->groupBy(DB::raw('SUBSTR(id_number,17,1) %2'))->get();
                break;

            case 4:
                $list = Volunteer::selectRaw('SUBSTR(id_number,17,1) %2 as sex, count(*)  as ct')->where('id_number', '!=', '')->groupBy(DB::raw('SUBSTR(id_number,17,1) %2'))->get();
                break;
            case 5:
                $list = Volunteer::selectRaw('SUBSTR(id_number,17,1) %2 as sex, count(*)  as ct')->where('id_number', '!=', '')->groupBy(DB::raw('SUBSTR(id_number,17,1) %2'))->get();
                break;
        }


        //女 男 未知
        $sex = [0,0,0];
        foreach($list as $value)
        {
            $sex[$value->sex] = $value->ct;
        }
        switch($user->type)
        {
            case 2 :
                $sex[2] = Volunteer::where('id_number', '')->whereRaw("user_id in ({$teamUserIds})")->count();
                break;

            case 3:
                $sex[2] = Volunteer::where('id_number', '')->where('org_id', $user->org_id)->count();
                break;

            case 4:
                $sex[2] = Volunteer::where('id_number', '')->count();
                break;
            case 5:
                $sex[2] = Volunteer::where('id_number', '')->count();
                break;
        }
        $data = CommonUtils::userGenderChartConfig($sex);
        return $this->returnJSON($data, 0, '');
    }

    // 志愿者 年龄  统计分析
    public  function ageVolunteer(Request $request) {
        $user = $request->user();
        $subQuery = DB::table('volunteer')->selectRaw("case
               when (date_format(now(), '%Y') - substr(id_number, 7, 4)) < 20 then 0
               when (date_format(now(), '%Y') - substr(id_number, 7, 4)) < 30 then 1
               when (date_format(now(), '%Y') - substr(id_number, 7, 4)) < 40 then 2
               when (date_format(now(), '%Y') - substr(id_number, 7, 4)) < 50 then 3
               when (date_format(now(), '%Y') - substr(id_number, 7, 4)) >= 50 then 4
                 end as age_type");
        switch($user->type)
        {
            case 2 :
                $teamUserIds = $this->_getTeamUserId($user->team_id);
                $subQuery = $subQuery->whereRaw("user_id in ({$teamUserIds})");
                break;

            case 3:
                $subQuery = $subQuery->where('org_id', '=', $user->org_id);
                break;
        }

//        dd(DB::raw("({$subQuery->toSQL()}) as a"));

        $list = DB::table(DB::raw("({$subQuery->toSQL()}) as a"))->mergeBindings($subQuery)->selectRaw('count(age_type) as ct, age_type')->groupby('age_type')->get()->toArray();
//        dd($list);
        //0~19, 20~29 30~39 40~49 50~
        $age = [0,0,0,0,0];
        foreach ($list as $item) {
            $age[$item->age_type] = $item->ct;
        }

        $ageData = [];
        $label = [ '20岁以下','20~29岁','30~39岁','40~49岁','50岁以上'];
        for($i=0;$i<count($age);$i+=1){
            $ageData[] = [
                'name' => $label[$i],
                'value' => $age[$i]
            ];
        }

        $data = CommonUtils::userAgeChartConfig($ageData, $label);
        return $this->returnJSON($data, 0, '');
    }

    // 志愿者 省份  统计分析
    public function areaVolunteer(Request $request) {
        $user = $request->user();
        $query = DB::table('volunteer')->selectRaw('count(province_name) as ct, province_name');
        switch($user->type)
        {
            case 2 :
                $teamUserIds = $this->_getTeamUserId($user->team_id);
                $query = $query->whereRaw("user_id in ({$teamUserIds})");
                break;

            case 3:
                $query = $query->where('org_id', '=', $user->org_id);
                break;
        }
        $list = $query->groupby('province_name')->get()->toArray();
//        dd($list);
        $area = $this->_area($list);
        $maxData = max($area);
        $areaData = [];
        foreach ($area as $key => $value) {
            $areaData[] = [
                'name' => $key,
                'value' => $value
            ];
        }

        $data = CommonUtils::userAreaChartConfig($areaData, $maxData);

        return $this->returnJSON($data, 0, '');
    }

    // 志愿者 城市  统计分析
    public function cityVolunteer(Request $request) {
        $user = $request->user();
        $query = DB::table('volunteer')->selectRaw('count(city_name) as ct, province_name, city_name');
        switch($user->type)
        {
            case 2 :
                $teamUserIds = $this->_getTeamUserId($user->team_id);
                $query = $query->whereRaw("user_id in ({$teamUserIds})");
                break;

            case 3:
                $query = $query->where('org_id', '=', $user->org_id);
                break;
        }
        $list = $query->groupby('city_name')->get()->toArray();

        $cityName = $request->get('city_name', '全国');
        $data = $this->_city($list, $cityName);

        $orderBy = $request->get('orderBy');
        $orderType = $request->get('orderDir');
        if ($orderBy) {
            $valueSort = [];
            foreach ($data as $value) {
                $valueSort[] = $value['value'];
            }
            array_multisort($valueSort, $orderType === 'asc' ? SORT_ASC :SORT_DESC, SORT_NUMERIC, $data);

        }

        return $this->returnJSON($data, 0, '');
    }

    //志愿团队 增长 统计分析
    public function increaseTeam(Request $request)
    {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if ($request->has('date_range')){
            $dateRange = array_map(function($item){
                return intval($item);
            }, explode(',', $request->get('date_range')));
            $begin = $dateRange[0];
            $end = $dateRange[1];
        } else {
            $begin = strtotime('-60 day');
            $end = time();
        }

        $beginDay = date('Y-m-d', $begin);
        $endDay = date('Y-m-d', $end);
//        dd($begin, $endDay);
        if($user->type == 2 )
        {
            return new Exception("权限不足");
        }

        $list = [];
        switch ($user->type)
        {
            case 3:

                $list = DB::table(DB::raw('team as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from team as t2 where org_id='.$user->org_id.' and DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("org_id = {$user->org_id} and DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:
                $list = DB::table(DB::raw('team as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from team as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')'.($org_id ? "and org_id = {$org_id}" : '').') as total ')->whereraw("DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'".($org_id ? "and org_id ={$org_id} " : ''))->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:
                $list = DB::table(DB::raw('team as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from team as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')'.($org_id ? "and org_id = {$org_id}" : '').') as total ')->whereraw("created_at >= '{$beginDay}' and created_at <= '{$endDay}'".($org_id ? "and org_id ={$org_id} " : ''))->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount = $this->_dayCount($list, $beginDay, $endDay);

        $addData = [];
        $totalData = [];
        $dateRange = [];

        foreach($dayCount as $item) {
            array_push($addData, $item['ct']);
            array_push($totalData, $item['total']);
            array_push($dateRange, $item['day']);
        }
//        dd($totalData);
        $chart = new CommonUtils();
        $data = $chart->userIncreaseChartConfig($dateRange, $addData, $totalData, ['新增团队数', '累计团队数']);
        return $this->returnJSON($data, 0, '');
    }

    // 志愿团队 所属类型  统计分析
    public function typeTeam(Request $request) {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if($user->type == 2) {
            return new Exception('权限不足');
        }
        $teamType = [];
        switch ($user->type)
        {
            case 3:
                $teamType = Team::where('org_id', $user->org_id)->selectRaw('type, count(*) as ct')->groupBy('type')->get()->toArray();
                break;
            case 4:
                $teamType = Team::selectRaw('type, count(*) as ct')->groupBy('type');
                if($org_id) {
                    $teamType = $teamType->where('org_id', $org_id);
                }
                $teamType = $teamType->get()->toArray();
                break;
            case 5:
                $teamType = Team::selectRaw('type, count(*) as ct')->groupBy('type');
                if($org_id) {
                    $teamType = $teamType->where('org_id', $org_id);
                }
                $teamType = $teamType->get()->toArray();
                break;
        }
        $data = [];
        $label = [];
        foreach ($teamType as $item) {
            if(!$item) continue; // 过滤未知类型
            array_unshift($label, $item['type'] ? $item['type'] : '未知') ;
            array_unshift($data, $item['ct']);
        }
        $data = CommonUtils::teamTypeChartConfig($data, $label);
        return $this->returnJSON($data);
    }

    // 志愿团队 所属机构  统计分析
    public function orgTeam(Request $request) {
        $user = $request->user();
        if($user->type != 4 && $user->type != 5) {
            return new Exception('权限不足');
        }
        $list = Team::with('org')->selectRaw('count(*) as ct, org_id')->groupBy('org_id')->get();
        $data = [];
        $label = [];
        foreach ($list as $value) {
            if(!$value || !$value->org || !$value->org->name) continue; // 过滤未知类型
            array_push($label, $value->org->name) ;
            array_push($data, $value->ct);
        }
        $data = CommonUtils::teamTypeChartConfig($data, $label, ['团队数量'], ['#5ab1ef']);
        return $this->returnJSON($data);
    }

    // 志愿团队 省份  统计分析
    public function areaTeam(Request $request) {
        $user = $request->user();
        if($user->type == 2) {
            return new Exception('权限不足');
        }
        $query = DB::table('team')->selectRaw('count(province_name) as ct, province_name');
        if($user->type == 3)
        {
            $query = $query->where('org_id', '=', $user->org_id);
        }
        $list = $query->groupby('province_name')->get()->toArray();
        $area = $this->_area($list);
        $maxData = max($area);
        $areaData = [];
        foreach ($area as $key => $value) {
            $areaData[] = [
                'name' => $key,
                'value' => $value
            ];
        }

        $data = CommonUtils::userAreaChartConfig($areaData, $maxData, ['团队数量']);

        return $this->returnJSON($data, 0, '');
    }

    // 志愿团队 城市  统计分析
    public function cityTeam(Request $request) {
        $user = $request->user();
        if($user->type == 2) {
            return new Exception('权限不足');
        }
        $query = DB::table('team')->selectRaw('count(city_name) as ct, province_name, city_name');
        if($user->type == 3)
        {
            $query = $query->where('org_id', '=', $user->org_id);
        }
        $list = $query->groupby('city_name')->get()->toArray();

        $cityName = $request->get('city_name', '全国');
        $data = $this->_city($list, $cityName);

        $orderBy = $request->get('orderBy');
        $orderType = $request->get('orderDir');
        if ($orderBy) {
            $valueSort = [];
            foreach ($data as $value) {
                $valueSort[] = $value['value'];
            }
            array_multisort($valueSort, $orderType === 'asc' ? SORT_ASC :SORT_DESC, SORT_NUMERIC, $data);

        }

        return $this->returnJSON($data, 0, '');
    }
    //团队信息统计
    public function team(Request $request)
    {
        $user = $request->user();

        $begin = $request->get('begin');
        $end = $request->get('end');

        if($user->type == 2 )
        {
            throw new \Exception('权限不足');
        }

        switch ($user->type)
        {
            case 3:
                $data['team_count'] = Team::where('org_id', $user->org_id)->count();
                $data['team_yesterday_count'] = Team::where('org_id', $user->org_id)->where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $list = DB::table(DB::raw('team as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from team as t2 where org_id='.$user->org_id.' and DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("org_id = {$user->org_id} and created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:
                $data['team_count'] = Team::query()->count();
                $data['team_yesterday_count'] = Team::where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $list = DB::table(DB::raw('team as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from team as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:
                $data['team_count'] = Team::query()->count();
                $data['team_yesterday_count'] = Team::where('created_at', 'like', date('Y-m-d', time()-86400))->count();

                $list = DB::table(DB::raw('team as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from team as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount  = [];

        foreach($list as $value)
        {
            $dayCount[$value->day] = [
                'day'=>$value->day,
                'ct'=>$value->ct,
                'total'=>$value->total,
            ];
        }

        $begin = strtotime($begin);
        $end = strtotime($end);
        if ($begin && $end) {
            for ($i = $begin; $i <= $end; $i = $i + 86400) {
                $day = date('Y-m-d', $i);

                if (!isset($dayCount[$day])) {
                    if ($i == $begin) {
                        $dayCount[$day] = [
                            'day' => $day,
                            'ct' => 0,
                            'total' => $list['0']->total - $list['0']->ct,
                        ];
                    } else {
                        $pre = date('Y-m-d', $i - 86400);

                        $dayCount[$day] = [
                            'day' => $day,
                            'ct' => 0,
                            'total' => $dayCount[$pre]['total'],
                        ];
                    }
                }
            }
        }
        ksort($dayCount);

        switch ($user->type)
        {
            case 3:
                $teamType = Team::where('org_id', $user->org_id)->selectRaw('type, count(*) as ct')->groupBy('type')->get()->toArray();

                $list = Team::where('org_id', $user->org_id)->get();
                break;

            case 4:
                $teamType = Team::selectRaw('type, count(*) as ct')->groupBy('type')->get()->toArray();
                $list = Team::query()->get();
                break;
            case 5:
                $teamType = Team::selectRaw('type, count(*) as ct')->groupBy('type')->get()->toArray();
                $list = Team::query()->get();
                break;
        }

        $area = [];
        foreach($list as $value)
        {
            if (!isset($area[$value->province_name])) {
                $area[$value->province_name] = [
                    'count' => 0,
                    'list' => [],
                ];
            }

            $area[$value->province_name]['count']++;
            if (!isset($area[$value->province_name]['list'][$value->city_name])) {
                $area[$value->province_name]['list'][$value->city_name] = 0;
            }
            $area[$value->province_name]['list'][$value->city_name]++;
        }

        $data['day_team_count'] = $dayCount;
        $data['team_type'] = $teamType;
        $data['area'] = $area;

        return $this->returnJSON($data, 0);
    }


    //志愿团队 增长 统计分析
    public function increaseProject(Request $request)
    {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if ($request->has('date_range')){
            $dateRange = array_map(function($item){
                return intval($item);
            }, explode(',', $request->get('date_range')));
            $begin = $dateRange[0];
            $end = $dateRange[1];
        } else {
            $begin = strtotime('-60 day');
            $end = time();
        }

        $beginDay = date('Y-m-d', $begin);
        $endDay = date('Y-m-d', $end);
//        dd($begin, $endDay);
        if($user->type == 2 )
        {
            return new Exception("权限不足");
        }

        switch ($user->type)
        {
            case 3:

                $list = DB::table(DB::raw('project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from project as t2 where org_id='.$user->org_id.' and DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("org_id = {$user->org_id} and DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:

                $list = DB::table(DB::raw('project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')'.($org_id ? "and org_id = {$org_id}" : '').') as total ')->whereraw("DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'".($org_id ? "and org_id ={$org_id} " : ''))->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount = $this->_dayCount($list, $beginDay, $endDay);

        $addData = [];
        $totalData = [];
        $dateRange = [];

        foreach($dayCount as $item) {
            array_push($addData, $item['ct']);
            array_push($totalData, $item['total']);
            array_push($dateRange, $item['day']);
        }
//        dd($totalData);
        $chart = new CommonUtils();
        $data = $chart->userIncreaseChartConfig($dateRange, $addData, $totalData, ['新增项目数', '累计项目数']);
        return $this->returnJSON($data, 0, '');
    }

    // 项目 类型 统计
    public function typeProject(Request $request) {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if($user->type == 2) {
            return new Exception('权限不足');
        }
        $projectType = [];
        $list = [];
        switch ($user->type)
        {
            case 3:
                $list = Project::where('org_id', $user->org_id)->get();
                break;

            case 4:
                $list = Project::query()->get();
                break;
            case 5:
                $list = Project::query()->get();
                break;
        }

        foreach($list as $value)
        {

            $type = explode(',', $value->project_type);
            foreach($type as $v)
            {
                if (!isset($projectType[$v])) {
                    $projectType[$v] = 0;
                }
                $projectType[$v]++;
            }
        }
        $data = [];
        $label = [];
        foreach ($projectType as $key => $value) {
            if(!$key) continue; // 过滤未知类型
            array_unshift($label, $key) ;
            array_unshift($data, $value);
        }
        $data = CommonUtils::teamTypeChartConfig($data, $label, ['项目数量']);
        return $this->returnJSON($data);
    }

    public function orgProject(Request $request) {
        $user = $request->user();
        if($user->type != 4) {
            return new Exception('权限不足');
        }
        $list = Project::with('org')->selectRaw('count(*) as ct, org_id')->groupBy('org_id')->get();
        $data = [];
        $label = [];
        foreach ($list as $value) {
            if(!$value || !$value->org || !$value->org->name) continue; // 过滤未知类型
            array_push($label, $value->org->name) ;
            array_push($data, $value->ct);
        }
        $data = CommonUtils::teamTypeChartConfig($data, $label, ['项目数量'], ['#5ab1ef']);
        return $this->returnJSON($data);
    }
    // 项目 省份  统计分析
    public function areaProject(Request $request) {
        $user = $request->user();
        $list = [];
        switch ($user->type)
        {
            case 3:
                $list = Project::where('org_id', $user->org_id)->get();
                break;

            case 4:
                $list = Project::query()->get();
                break;
            case 5:
                $list = Project::query()->get();
                break;
        }
        $area = $this->_area($list);
        $maxData = max($area);
        $areaData = [];
        foreach ($area as $key => $value) {
            $areaData[] = [
                'name' => $key,
                'value' => $value
            ];
        }

        $data = CommonUtils::userAreaChartConfig($areaData, $maxData, ['项目数量']);

        return $this->returnJSON($data, 0, '');
    }

    // 项目 城市  统计分析
    public function cityProject(Request $request) {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        $list = [];
        switch ($user->type)
        {
            case 3:
                $list = Project::where('org_id', $user->org_id)->get();
                break;

            case 4:
                $list = Project::query()->get();
                break;
            case 5:
                $list = Project::query()->get();
                break;
        }
        $cityName = $request->get('city_name', '全国');
        $data = $this->_city($list, $cityName);

        $orderBy = $request->get('orderBy');
        $orderType = $request->get('orderDir');
        if ($orderBy) {
            $valueSort = [];
            foreach ($data as $value) {
                $valueSort[] = $value['value'];
            }
//            ksort($data, );
//            dd($valueSort, $data);
            array_multisort($valueSort, $orderType === 'asc' ? SORT_ASC :SORT_DESC, SORT_NUMERIC, $data);

        }
//        dd($data);

        return $this->returnJSON($data, 0, '');
    }

    //项目
    public function project(Request $request)
    {
        $user = $request->user();

        $begin = $request->get('begin');
        $end = $request->get('end');

        if($user->type == 2 )
        {
            throw new \Exception('权限不足');
        }

        switch ($user->type)
        {
            case 3:
                $data['project_count'] = Project::where('org_id', $user->org_id)->count();
                $data['project_yesterday_count'] = Project::where('org_id', $user->org_id)->where('created_at', 'like', date('Y-m-d', strtotime()-86400))->count();

                $list = DB::table(DB::raw('project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from project as t2 where org_id='.$user->org_id.' and DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("org_id = {$user->org_id} and created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:
                $data['project_count'] = Project::query()->count();
                $data['project_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', strtotime()-86400))->count();

                $list = DB::table(DB::raw('project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:
                $data['project_count'] = Project::query()->count();
                $data['project_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', strtotime()-86400))->count();

                $list = DB::table(DB::raw('project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount  = [];

        foreach($list as $value)
        {
            $dayCount[$value->day] = [
                'day'=>$value->day,
                'ct'=>$value->ct,
                'total'=>$value->total,
            ];
        }

        $begin = strtotime($begin);
        $end = strtotime($end);
        for($i = $begin; $i <= $end; $i=$i+86400)
        {
            $day = date('Y-m-d', $i);

            if(!isset($dayCount[$day]))
            {
                if($i == $begin)
                {
                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$list['0']->total - $list['0']->ct,
                    ];
                }else{
                    $pre = date('Y-m-d', $i - 86400);

                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$dayCount[$pre]['total'],
                    ];
                }
            }
        }
        ksort($dayCount);

        switch ($user->type)
        {
            case 3:
                $list = Project::where('org_id', $user->org_id)->get();
                break;

            case 4:
                $list = Project::query()->get();
                break;
            case 5:
                $list = Project::query()->get();
                break;
        }

        $area = $projectType = [];
        foreach($list as $value)
        {

            $type = explode(',', $value->project_type);
            foreach($type as $value)
            {
                $projectType[$value]++;
            }
            $area[$value->province_name]['count']++;
            $area[$value->province_name]['list'][$value->city_name]++;
        }

        $data['day_project_count'] = $dayCount;
        $data['project_type'] = $projectType;
        $data['area'] = $area;

        return $this->returnJSON($data, 0);
    }

    //志愿团队 增长 统计分析
    public function increaseReward(Request $request)
    {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if ($request->has('date_range')) {
            $dateRange = array_map(function ($item) {
                return intval($item);
            }, explode(',', $request->get('date_range')));
            $begin = $dateRange[0];
            $end = $dateRange[1];
        } else {
            $begin = strtotime('-60 day');
            $end = time();
        }

        $beginDay = date('Y-m-d', $begin);
        $endDay = date('Y-m-d', $end);
//        dd($begin, $endDay);
        if ($user->type == 2) {
            return new Exception("权限不足");
        }

        $list = [];

        switch ($user->type) {
            case 3:

                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, sum(reward_time) as ct, (select count(reward_time) from user_project as t2 where project_id in (select id from project where org_id=' . $user->org_id . ') and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("project_id in (select id from project where org_id = {$user->org_id} ) and DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:

                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(reward_time) as ct, (select count(reward_time) from user_project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')' . ($org_id ? "and org_id = {$org_id}" : '') . ') as total ')->whereraw("DATE_FORMAT(created_at, '%Y-%m-%d') >= '{$beginDay}' and DATE_FORMAT(created_at, '%Y-%m-%d') <= '{$endDay}'" . ($org_id ? "and org_id ={$org_id} " : ''))->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount = $this->_dayCount($list, $beginDay, $endDay);

        $addData = [];
        $totalData = [];
        $dateRange = [];

        foreach ($dayCount as $item) {
            array_push($addData, $item['ct']);
            array_push($totalData, $item['total']);
            array_push($dateRange, $item['day']);
        }
//        dd($totalData);
        $chart = new CommonUtils();
        $data = $chart->userIncreaseChartConfig($dateRange, $addData, $totalData, ['新增时长', '累计时长']);
        return $this->returnJSON($data, 0, '');
    }

    //时长
    public function reward(Request $request)
    {
        $user = $request->user();

        $begin = $request->get('begin');
        $end = $request->get('end');

        if($user->type == 2 )
        {
            throw new \Exception('权限不足');
        }

        switch ($user->type)
        {
            case 3:
                $data['reward_count'] = UserProject::with('project')->whereHas('project', function($query) use ($user){
                    $query->where('org_id', $user->org_id);
                })->sum('reward_time');
                $data['reward_yesterday_count'] = UserProject::with('project')->whereHas('project', function($query) use ($user){
                    $query->where('org_id', $user->org_id);
                })->where('created_at', 'like', date('Y-m-d', strtotime()-86400))->sum('reward_time');

                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, sum(reward_time) as ct, (select count(reward_time) from user_project as t2 where project_id in (select id from project where org_id='.$user->org_id.') and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("project_id in (select id from project where org_id = {$user->org_id} ) and created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:
                $data['reward_count'] = UserProject::query()->sum('reward_time');
                $data['reward_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', time()-86400))->sum('reward_time');

                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(reward_time) as ct, (select count(reward_time) from user_project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:
                $data['reward_count'] = UserProject::query()->sum('reward_time');
                $data['reward_yesterday_count'] = Project::where('created_at', 'like', date('Y-m-d', time()-86400))->sum('reward_time');

                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(reward_time) as ct, (select count(reward_time) from user_project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount  = [];

        foreach($list as $value)
        {
            $dayCount[$value->day] = [
                'day'=>$value->day,
                'ct'=>$value->ct,
                'total'=>$value->total,
            ];
        }

        $begin = strtotime($begin);
        $end = strtotime($end);
        for($i = $begin; $i <= $end; $i=$i+86400)
        {
            $day = date('Y-m-d', $i);

            if(!isset($dayCount[$day]))
            {
                if($i == $begin)
                {
                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$list['0']->total - $list['0']->ct,
                    ];
                }else{
                    $pre = date('Y-m-d', $i - 86400);

                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$dayCount[$pre]['total'],
                    ];
                }
            }
        }
        ksort($dayCount);



        $data['day_reward_count'] = $dayCount;


        return $this->returnJSON($data, 0);
    }

    //项目报名人数 增长 统计分析
    public function dailyJoinProject(Request $request)
    {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if ($request->has('date_range')) {
            $dateRange = array_map(function ($item) {
                return intval($item);
            }, explode(',', $request->get('date_range')));
            $begin = $dateRange[0];
            $end = $dateRange[1];
        } else {
            $begin = strtotime('-60 day');
            $end = time();
        }

        $beginDay = date('Y-m-d', $begin);
        $endDay = date('Y-m-d', $end);
//        dd($begin, $endDay);
        if ($user->type == 2) {
            return new Exception("权限不足");
        }

        $list = [];

        switch ($user->type)
        {
            case 3:
                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where project_id in (select id from project where org_id='.$user->org_id.') and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("project_id in (select id from project where org_id = {$user->org_id} ) and created_at >= '{$beginDay}' and created_at <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at >= '{$beginDay}' and created_at <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at >= '{$beginDay}' and created_at <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount = $this->_dayCount($list, $beginDay, $endDay);

        $addData = [];
        $totalData = [];
        $dateRange = [];

        foreach ($dayCount as $item) {
            array_push($addData, $item['ct']);
            array_push($totalData, $item['total']);
            array_push($dateRange, $item['day']);
        }
//        dd($totalData);
        $chart = new CommonUtils();
        $data = $chart->userIncreaseChartConfig($dateRange, $addData, $totalData, ['新增报名人数', '累计报名人数']);
        return $this->returnJSON($data, 0, '');
    }

    //项目参与的人数 增长 统计分析
    public function dailyJoinReward(Request $request)
    {
        $user = $request->user();
        $org_id = $request->get('org_id', 0);
        if ($request->has('date_range')) {
            $dateRange = array_map(function ($item) {
                return intval($item);
            }, explode(',', $request->get('date_range')));
            $begin = $dateRange[0];
            $end = $dateRange[1];
        } else {
            $begin = strtotime('-60 day');
            $end = time();
        }

        $beginDay = date('Y-m-d', $begin);
        $endDay = date('Y-m-d', $end);
//        dd($begin, $endDay);
        if ($user->type == 2) {
            return new Exception("权限不足");
        }

        $list = [];

        switch ($user->type)
        {
            case 3:
                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where reward_time > 0 and  project_id in (select id from project where org_id='.$user->org_id.') and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("reward_time > 0 and  project_id in (select id from project where org_id = {$user->org_id} ) and created_at >= '{$beginDay}' and created_at <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where reward_time > 0 and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("reward_time > 0 and  created_at >= '{$beginDay}' and created_at <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where reward_time > 0 and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("reward_time > 0 and  created_at >= '{$beginDay}' and created_at <= '{$endDay}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount = $this->_dayCount($list, $beginDay, $endDay);

        $addData = [];
        $totalData = [];
        $dateRange = [];

        foreach ($dayCount as $item) {
            array_push($addData, $item['ct']);
            array_push($totalData, $item['total']);
            array_push($dateRange, $item['day']);
        }
//        dd($totalData);
        $chart = new CommonUtils();
        $data = $chart->userIncreaseChartConfig($dateRange, $addData, $totalData, ['新增参与人数', '累计参与人数']);
        return $this->returnJSON($data, 0, '');
    }
    //参与
    public function joinProject(Request $request)
    {
        $user = $request->user();

        $begin = $request->get('begin');
        $end = $request->get('end');

        $user->type = 4;
        if($user->type == 2 )
        {
            throw new \Exception('权限不足');
        }

        switch ($user->type)
        {
            case 3:
                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where project_id in (select id from project where org_id='.$user->org_id.') and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("project_id in (select id from project where org_id = {$user->org_id} ) and created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }

        $dayCount  = [];

        foreach($list as $value)
        {
            $dayCount[$value->day] = [
                'day'=>$value->day,
                'ct'=>$value->ct,
                'total'=>$value->total,
            ];
        }

        $begin = strtotime($begin);
        $end = strtotime($end);
        for($i = $begin; $i <= $end; $i=$i+86400)
        {
            $day = date('Y-m-d', $i);

            if(!isset($dayCount[$day]))
            {
                if($i == $begin)
                {
                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$list['0']->total - $list['0']->ct,
                    ];
                }else{
                    $pre = date('Y-m-d', $i - 86400);

                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$dayCount[$pre]['total'],
                    ];
                }
            }
        }
        ksort($dayCount);

        $data['day_join_count'] = $dayCount;

        $begin = $request->get('begin');
        $end = $request->get('end');
        switch ($user->type)
        {
            case 3:
                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where reward_time > 0 and  project_id in (select id from project where org_id='.$user->org_id.') and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("reward_time > 0 and  project_id in (select id from project where org_id = {$user->org_id} ) and created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;

            case 4:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where reward_time > 0 and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("reward_time > 0 and  created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
            case 5:


                $list = DB::table(DB::raw('user_project as t1'))->selectRaw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')  as day, count(*) as ct, (select count(*) from user_project as t2 where reward_time > 0 and  DATE_FORMAT(t2.created_at,\'%Y-%m-%d\')<=DATE_FORMAT(t1.created_at,\'%Y-%m-%d\')) as total ')->whereraw("reward_time > 0 and  created_at > '{$begin}' and created_at < '{$end}'")->groupby(DB::raw('DATE_FORMAT(t1.created_at,\'%Y-%m-%d\') '))->get();
                break;
        }


        $dayCount  = [];

        foreach($list as $value)
        {
            $dayCount[$value->day] = [
                'day'=>$value->day,
                'ct'=>$value->ct,
                'total'=>$value->total,
            ];
        }

        $begin = strtotime($request->get('begin'));
        $end = strtotime($request->get('end'));
        for($i = $begin; $i <= $end; $i=$i+86400)
        {
            $day = date('Y-m-d', $i);

            if(!isset($dayCount[$day]))
            {
                if($i == $begin)
                {
                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$list['0']->total - $list['0']->ct,
                    ];
                }else{
                    $pre = date('Y-m-d', $i - 86400);

                    $dayCount[$day] = [
                        'day'=>$day,
                        'ct'=>0,
                        'total'=>$dayCount[$pre]['total'],
                    ];
                }
            }
        }
        ksort($dayCount);

        $data['day_clock_count'] = $dayCount;

        return $this->returnJSON($data, 0);
    }

    private function _getTeamUserId($teamId)
    {
        $node = UserTeam::where('team_id', $teamId)->where('verify_status', 1)->selectRaw('GROUP_CONCAT( user_id) as ids')->first();

        if (!$node->ids) {
            return '0';
        }

        return $node->ids;
    }

    private function _area($list)
    {
        $area = ["北京"=>0, "天津" => 0, "上海" => 0, "重庆" => 0, "河北" => 0, "河南" => 0, "云南" => 0, "辽宁" => 0, "黑龙江" => 0, "湖南" => 0, "安徽" => 0,
            "山东" => 0, "新疆" => 0, "江苏" => 0, "浙江" => 0, "江西" => 0, "湖北" => 0, "广西" => 0, "甘肃" => 0, "山西" => 0, "内蒙古" => 0, "陕西" => 0, "吉林" => 0, "福建" => 0, "贵州" => 0,
            "广东" => 0, "青海" => 0, "西藏" => 0, "四川" => 0, "宁夏" => 0, "海南" => 0, "台湾" => 0,  "香港" => 0, "澳门" => 0, "南海诸岛" => 0];
        foreach ($list as $value) {
            if(isset($value->province_name) && $value->province_name){
                $provinceName = preg_replace('[市|省|自治区]', '', $value->province_name);
                if (!isset($area[$provinceName])) {
                    $area[$provinceName] = 0;
                }
                $area[$provinceName] += $value->ct;
            }

        }
        return $area;
    }

    private function _city($list, $cityName)
    {
        $area = ["北京"=>[], "天津" => [], "上海" => [], "重庆" => [], "河北" => [], "河南" => [], "云南" => [], "辽宁" => [], "黑龙江" => [], "湖南" => [], "安徽" => [],
            "山东" => [], "新疆" => [], "江苏" => [], "浙江" => [], "江西" => [], "湖北" => [], "广西" => [], "甘肃" => [], "山西" => [], "内蒙古" => [], "陕西" => [], "吉林" => [], "福建" => [], "贵州" => [],
            "广东" => [], "青海" => [], "西藏" => [], "四川" => [], "宁夏" => [], "海南" => [], "台湾" => [],  "香港" => [], "澳门" => [], "南海诸岛" => []];
        foreach ($list as $value) {
            if(isset($value->province_name) && $value->province_name){
                $provinceName = preg_replace('[市]', '', $value->province_name);
                if (!isset($area[$provinceName]['count'])) {
                    $area[$provinceName]['count'] = 0;
                }
                $area[$provinceName]['count'] += $value->ct;
                if (!isset($area[$provinceName]['list'][$value->city_name])){
                    $area[$provinceName]['list'][$value->city_name] = 0;
                }
                $area[$provinceName]['list'][$value->city_name] += $value->ct;
            }

        }
        $data = [];
        $total = 0;
        $cityName = $cityName ? $cityName : '全国';
        foreach ($area as $key => $city) {
            if($cityName === '全国') {
                $val = isset($city['count']) ? $city['count'] : 0;
                if (!$val) continue;
                $data[] = [
                    'city' => $key,
                    'value' => $val,
                    'progress' => 0
                ];
                $total += $val;
            }
            if($cityName === $key){
                if(!isset($city['list'])) break;
                foreach ($city['list'] as $c => $n){
                    $data[] = [
                        'city' => $c,
                        'value' => $n,
                        'progress' => 0
                    ];
                }
                $total = $city['count'];
                break;
            }
        }
//        dd($total);
        foreach ($data as &$item) {
            $item['progress'] = intval( $item['value'] / $total * 100);
        }
        return $data;
    }

    /**
     * @deprecated 弃用，效率极低
     * @param $user
     * @return Volunteer[]|\Illuminate\Database\Eloquent\Collection|null
     */
    private function getVolunteer($user) {
        $list = null;
        switch($user->type)
        {
            case 2 :
                $teamUserIds = $this->_getTeamUserId($user->team_id);
                $list = Volunteer::select('id_number','province_name','city_name')->where('id_number', '!=', '')->whereRaw("user_id in ({$teamUserIds})")->get();
                break;

            case 3:
                $list = Volunteer::select('id_number','province_name','city_name')->where('id_number', '!=', '')->where('org_id', $user->org_id)->get();
                break;

            case 4:
                $list = Volunteer::where('id_number', '!=', '')->get();
                break;
            case 5:
                $list = Volunteer::where('id_number', '!=', '')->get();
                break;
        }
        return $list;
    }

    public function sug(Request $request, $term = '') {

        $builder = Area::query()->select('name')->where('parent_id', 0);

        if (strlen($term)) {
            $builder->where('name', 'like', '%'.$term.'%');
        }

        $nodes = $builder->get()->toArray();
        $data = array_map(function($node) {
            return [
                'value' => $node['name'],
                'label' => $node['name']
            ];
        }, $nodes);
        array_unshift($data, [
            'value' => '全国',
            'label' => '全国',
        ]);
        return $this->returnJSON($data, 0, '');
    }

    private function _dayCount($list, $beginDay, $endDay)
    {
        $dayCount  = [];
        if (count($list)) {
            foreach ($list as $value) {
                $dayCount[$value->day] = [
                    'day' => $value->day,
                    'ct' => $value->ct,
                    'total' => $value->total,
                ];
            }
        }

//        dd($begin, $end);

        $begin = strtotime($beginDay);
        $end = strtotime($endDay);
//        dd($begin, $end);
        if($begin && $end) {
            for ($i = $begin; $i <= $end; $i = $i + 86400) {
                $day = date('Y-m-d', $i);

                if (!isset($dayCount[$day])) {
                    if (count($list) <= 0) {
                        $dayCount[$day] = [
                            'day' => 0,
                            'ct' => 0,
                            'total' => 0,
                        ];
                        continue;

                    }
                    if ($i == $begin) {
                        $dayCount[$day] = [
                            'day' => $day,
                            'ct' => 0,
                            'total' => $list['0']->total - $list['0']->ct,
                        ];
                    } else {
                        $pre = date('Y-m-d', $i - 86400);

                        $dayCount[$day] = [
                            'day' => $day,
                            'ct' => 0,
                            'total' => $dayCount[$pre]['total'],
                        ];
                    }
                }
            }
        }
        ksort($dayCount);
        return $dayCount;
    }

}
